//#define TRACE
using System;
using System.Data;
using System.ComponentModel;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Dimok;
using Dimok.Data;
using Dimok.Data.Database;
namespace Dimok.Data.DataSources {

	////////////////////////////////////////////////////////////////////////////////////////////////////
	/// <summary>	Bitfield of flags for specifying EmptyRowPolicyType. </summary>
	///
	/// <remarks>	Dimon, 12/21/2010. </remarks>
	////////////////////////////////////////////////////////////////////////////////////////////////////

	[Flags]
	public enum EmptyRowPolicyType { None = 0, First = 1, Last = 2, OnlyIfEmpty = 4,OnlyIfMoreThenOne = 8 }

	////////////////////////////////////////////////////////////////////////////////////////////////////
	/// <summary>	An object data source base. </summary>
	///
	/// <remarks>	Dimon, 12/21/2010. </remarks>
	////////////////////////////////////////////////////////////////////////////////////////////////////

	public abstract class AObjectDataSourceBase : ObjectDataSource {
		protected const string SELECT_METHOD = "SelectEx";
		protected const string DELETE_METHOD = "DeleteEx";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the connection string. </summary>
		///
		/// <value>	The connection string. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public abstract string ConnectionString { get; }
		public int SelectTimeOut = 0;
		private string _dataTableName = "";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the name of the data table. </summary>
		///
		/// <value>	The name of the data table. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual string DataTableName { get { return _dataTableName; } set { _dataTableName = value; } }
		protected Page _pageObject;

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	
		/// Gets or sets a reference to the <see cref="T:System.Web.UI.Page" /> instance that contains the
		/// server control. 
		/// </summary>
		///
		/// <value>	The page. </value>
		///
		/// ### <returns>	
		/// The <see cref="T:System.Web.UI.Page" /> instance that contains the server control. 
		/// </returns>
		/// ### <exception cref="T:System.InvalidOperationException">	The control is a <see cref="T:
		/// 																													System.Web.UI.WebControls.Substitution"
		/// 																													/> control. </exception>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public new Page Page { get { return _pageObject == null ? base.Page : _pageObject; } set { _pageObject = value; } }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Default constructor. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public AObjectDataSourceBase() {
			TypeName = GetType().FullName;
			SelectMethod = SELECT_METHOD;
			DeleteMethod = DELETE_METHOD;
			StopWatch = new System.Diagnostics.Stopwatch();
		}

		#region Overrides

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Raises the initialise event. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="e">	Event information to send to registered event handlers. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected override void OnInit(EventArgs e) {
			base.OnInit(e);
			ObjectCreated += new ObjectDataSourceObjectEventHandler(OnObjectCreated);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the object created action. </summary>
		///
		/// <param name="sender">	Source of the event. </param>
		/// <param name="e">			Event information to send to registered event handlers. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected virtual void OnObjectCreated(object sender, ObjectDataSourceEventArgs e) {
			AObjectDataSourceBase me = e.ObjectInstance as AObjectDataSourceBase;
			me.UpdateCommandTimeout = UpdateCommandTimeout;
			me.InsertCommandTimeout = InsertCommandTimeout;
			try {
				me.DataTableName = DataTableName;
			} catch (NotImplementedException) { }
			me.EmtyRowPolicy = EmtyRowPolicy;
			me.SelectPostfix = SelectPostfix;
			me.OrderBy = OrderBy;
			try {
				me.SelectViewName = SelectViewName;
			} catch { }
			me.SelectList = SelectList;
			me.UpdateList = UpdateList;
			try {
				me.DeleteViewName = DeleteViewName;
			} catch { }
			me.InsertList = InsertList;
			try {
				me.InsertViewName = InsertViewName;
			} catch { }
			try {
				me.UseNoLock = UseNoLock;
			} catch { }
			me.Filters = Filters;
			me.Filter = Filter;
			me.FilterDefault = FilterDefault;
			me.Where = Where;
			me.Page = Page;
		}
		#endregion

		#region Properties
		private bool showRowNumber = false;

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets a value indicating whether the row number is shown. </summary>
		///
		/// <value>	true if show row number, false if not. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public bool ShowRowNumber { get { return showRowNumber; } set { showRowNumber = value; } }
		protected System.Diagnostics.Stopwatch StopWatch;

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Values that represent SelectViewKindType. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected enum SelectViewKindType { Table, Function }
		protected SelectViewKindType SelectViewKind = SelectViewKindType.Table;
		private bool useNoLock = true;

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets a value indicating whether this object use no lock. </summary>
		///
		/// <value>	true if use no lock, false if not. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual bool UseNoLock { get { return useNoLock; } set { useNoLock = value; } }
		private const string WITH_NOLOCK = " WITH(nolock)";
		//private bool WithNoLock = true;
		private int _updateCommandTimeout = 30;

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the update command timeout. </summary>
		///
		/// <value>	The update command timeout. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public int UpdateCommandTimeout { get { return _updateCommandTimeout; } set { _updateCommandTimeout = value; } }

		private int _insertCommandTimeout = 30;

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the insert command timeout. </summary>
		///
		/// <value>	The insert command timeout. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public int InsertCommandTimeout { get { return _insertCommandTimeout; } set { _insertCommandTimeout = value; } }

		private int _deleteCommandTimeout = 30;

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the delete command timeout. </summary>
		///
		/// <value>	The delete command timeout. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public int DeleteCommandTimeout { get { return _deleteCommandTimeout; } set { _deleteCommandTimeout = value; } }

		private string _selectPostfix = "";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the select postfix. </summary>
		///
		/// <value>	The select postfix. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string SelectPostfix { get { return _selectPostfix; } set { _selectPostfix = value; } }

		private string _selectIDFieldName = "ID";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the name of the select identifier field. </summary>
		///
		/// <value>	The name of the select identifier field. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string SelectIDFieldName { get { return _selectIDFieldName; } set { _selectIDFieldName = value; } }
		private string _deleteIDFieldName = "ID";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the name of the delete identifier field. </summary>
		///
		/// <value>	The name of the delete identifier field. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string DeleteIDFieldName { get { return _deleteIDFieldName; } set { _deleteIDFieldName = value; } }

		/// <summary>
		/// Empty row is added when DataTable has no records.
		/// Set this to False to alwais add an empty row
		/// <value>True</value>
		/// </summary>
		protected EmptyRowPolicyType _emtyRowPolicy = EmptyRowPolicyType.OnlyIfEmpty;

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the emty row policy. </summary>
		///
		/// <value>	The emty row policy. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		[Bindable(true), Description("Add empty line to DataView")]
		public virtual EmptyRowPolicyType EmtyRowPolicy { get { return _emtyRowPolicy; } set { _emtyRowPolicy = value; } }

		public List<string> OrderBy = new List<string>();

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the order bys. </summary>
		///
		/// <value>	The order bys. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string OrderBys {
			get {
				List<string> obs = new List<string>();
				foreach (string ob in OrderBy)
					if (ob == "") continue;
					else
						foreach (string orderBy in ob.Split(',')) {
							Match m = Regex.Match(orderBy, "(?<field>.+)(?<dir>\\s+ASC|\\s+DESC)?", RegexOptions.IgnoreCase | RegexOptions.RightToLeft);
							string s = m.Groups["field"].Value;
							if (!s.StartsWith("[")) s = "[" + s + "]";
							s += " " + m.Groups["dir"].Value;
							if (s != "" && !obs.Contains(s)) obs.Add(s);
						}
				return obs.Count == 0 ? "" : " ORDER BY " + string.Join(",", obs.ToArray());
			}
			set {
				if (value != "") {
					OrderBy.Clear();
					OrderBy.Add(value);
				}
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Dictionary of filters. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public class FiltersDictionary : OrderedDictionary {
			public override string ToString() {
				List<string> sl = new List<string>();
				foreach (DictionaryEntry de in this)
					if ((de.Value + "") != "") sl.Add(de.Key + "" + de.Value);
				return string.Join(" AND ", sl.ToArray());
			}
		}
    public FiltersDictionary Filters = new FiltersDictionary();

		private string _filter = "";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the filter. </summary>
		///
		/// <value>	The filter. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual string Filter { get { return _filter; } set { _filter = value; } }
		private string _filterDefault = "";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the filter default. </summary>
		///
		/// <value>	The filter default. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string FilterDefault { get { return _filterDefault; }
			set { if ((_filterDefault = value) == null)throw new NoNullAllowedException("FilterDefault must not be Null."); }
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	List of searches. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public class SearchList : List<string> {
			public SearchList() : base() { }
			public SearchList(IEnumerable<string> list) : base(list) { }
			public new void Add(string filter) {
				if (!base.Contains(filter) && (filter + "") != "")
					base.Add(filter);
			}
		}
		public SearchList Where = new SearchList();

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Wheres. </summary>
		///
		/// <param name="wheres">	A variable-length parameters list containing wheres. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string Wheres(params object[] wheres) {
			SearchList lst = new SearchList(Where);
			lst.Add(Filter);
			lst.Add(Filters.ToString());
			foreach (string where in wheres)
				if ((where + "") != "") lst.Add(SQLHelperEx.CheckInjects(where));
			if (lst.Count == 0 && FilterDefault != "") lst.Add(SQLHelperEx.CheckInjects(FilterDefault));
			return lst.Count == 0 ? "" : " WHERE (" + string.Join(") AND (", lst.ToArray())+")";
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Query if 'text' is empty. </summary>
		///
		/// <param name="text">	The text. </param>
		///
		/// <returns>	true if empty, false if not. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static bool IsEmpty(string text) { return text == ""; }

		private string _selectList = "*";
		[Bindable(true)]

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	List for SELECT [List] FROM TABLE statement. * - defualt. </summary>
		///
		/// <value>	A List of selects. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string SelectList { get { return _selectList + (ShowRowNumber ? ",ROW_NUMBER() OVER (" + OrderBys + ") Row" : ""); } set { _selectList = value; } }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Builds a select. </summary>
		///
		/// <param name="wheres">	A variable-length parameters list containing wheres. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected string BuildSelect(params object[] wheres) {
			string viewName = SelectViewName + SelectPostfix;
			if (UseNoLock) viewName += WITH_NOLOCK;
			return "SELECT " + SelectList + " FROM " + viewName + Wheres(wheres) + OrderBys;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Builds a select. </summary>
		///
		/// <param name="keyFilter">	The key filter. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected string BuildSelect(KeysFilter keyFilter) {
			string viewName = SelectViewName + SelectPostfix;
			if (UseNoLock) viewName += WITH_NOLOCK;
			return "SELECT " + SelectList + " FROM " + viewName + " WHERE " + keyFilter.Filter;
		}
		private string _updateList = "*";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets a list of updates. </summary>
		///
		/// <value>	A List of updates. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string UpdateList { get { return _updateList; } set { _updateList = value; } }
		private string _insertList = "*";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets a list of inserts. </summary>
		///
		/// <value>	A List of inserts. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string InsertList { get { return _insertList; } set { _insertList = value; } }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Builds a update. </summary>
		///
		/// <param name="wheres">	A variable-length parameters list containing wheres. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected string BuildUpdate(params object[] wheres) {
			SearchList lst = new SearchList();
			foreach (string w in wheres)
				lst.Add(w);
			string where = lst.Count == 0 ? "" : " WHERE " + string.Join(" AND ", lst.ToArray());
			return "SELECT " + UpdateList + " FROM " + UpdateViewName + WITH_NOLOCK + where;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Builds a update. </summary>
		///
		/// <param name="keyFilter">	The key filter. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected string BuildUpdate(KeysFilter keyFilter) {
			return "SELECT " + (keyFilter.Params.Length > 0 ? UpdateList : InsertList) +
						 " FROM " + (keyFilter.Params.Length > 0 ? UpdateViewName : InsertViewName) +
						 WITH_NOLOCK +
						 " WHERE " + keyFilter.Filter;
		}
		#endregion

		#region DataSource Parameters

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	View name to use in autogenerated INSERT stattement. </summary>
		///
		/// <param name="paramName">	Name of the parameter. </param>
		/// <param name="paramValue">	The parameter value. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected void SetUpdateParameter(string paramName, object paramValue) {
			SetSelectParameter(TypeCode.String, paramName, paramValue);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	View name to use in autogenerated INSERT stattement. </summary>
		///
		/// <param name="typeCode">		The type code. </param>
		/// <param name="paramName">	Name of the parameter. </param>
		/// <param name="paramValue">	The parameter value. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected void SetUpdateParameter(TypeCode typeCode, string paramName, object paramValue) {
			SetParameter(UpdateParameters, typeCode, paramName, paramValue);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sets a select parameter. </summary>
		///
		/// <param name="paramName">	Name of the parameter. </param>
		/// <param name="paramValue">	The parameter value. </param>
		/// <param name="nullIfs">		A variable-length parameters list containing null ifs. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected void SetSelectParameter(string paramName, object paramValue,params object[] nullIfs) {
			foreach (object nullIf in nullIfs)
				if ((paramValue + "") == (nullIf + "")) {
					RemSelectParameter(paramName);
					return;
				}
			SetSelectParameter(TypeCode.String, paramName, paramValue);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sets a parameter. </summary>
		///
		/// <param name="parameters">	Options for controlling the operation. </param>
		/// <param name="paramName">	Name of the parameter. </param>
		/// <param name="paramValue">	The parameter value. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected void SetParameter(ParameterCollection parameters, string paramName, object paramValue) {
			SetParameter(parameters, TypeCode.String, paramName, paramValue);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sets a select parameter. </summary>
		///
		/// <param name="typeCode">		The type code. </param>
		/// <param name="paramName">	Name of the parameter. </param>
		/// <param name="paramValue">	The parameter value. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected void SetSelectParameter(TypeCode typeCode, string paramName, object paramValue) {
			SetParameter(SelectParameters, typeCode, paramName, paramValue);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sets a parameter. </summary>
		///
		/// <param name="parameters">	Options for controlling the operation. </param>
		/// <param name="typeCode">		The type code. </param>
		/// <param name="paramName">	Name of the parameter. </param>
		/// <param name="paramValue">	The parameter value. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected void SetParameter(ParameterCollection parameters, TypeCode typeCode, string paramName, object paramValue) {
			if (SelectParameters[paramName] != null) SelectParameters[paramName].DefaultValue = paramValue + "";
			else SelectParameters.Add(paramName, typeCode, paramValue + "");
			for (int i = 0; i < parameters.Count - 1; i++)
				if (parameters[i].Name == "Filter") {
					Parameter param = parameters[i];
					parameters.RemoveAt(i);
					parameters.Add(param);
					break;
				}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a select parameter int. </summary>
		///
		/// <param name="paramName">	Name of the parameter. </param>
		///
		/// <returns>	The select parameter int. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected int GetSelectParameterInt(string paramName) {
			try {
				return int.Parse("0" + GetParameter(SelectParameters, paramName));
			} catch (Exception exc) {
				exc.Data.Add("ParamName", paramName);
				exc.Data.Add("ParamValue", GetParameter(SelectParameters, paramName));
				throw;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a select parameter date. </summary>
		///
		/// <param name="paramName">	Name of the parameter. </param>
		///
		/// <returns>	The select parameter date. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected DateTime GetSelectParameterDate(string paramName) { return DateTime.Parse(GetSelectParameterString(paramName)); }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a select parameter string. </summary>
		///
		/// <param name="paramName">	Name of the parameter. </param>
		///
		/// <returns>	The select parameter string. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected string GetSelectParameterString(string paramName) { return GetParameter(SelectParameters, paramName) + ""; }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a select parameter. </summary>
		///
		/// <param name="paramName">	Name of the parameter. </param>
		///
		/// <returns>	The select parameter. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected string GetSelectParameter(string paramName) { return GetParameter(SelectParameters, paramName); }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a parameter. </summary>
		///
		/// <param name="parameters">	Options for controlling the operation. </param>
		/// <param name="paramName">	Name of the parameter. </param>
		///
		/// <returns>	The parameter. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected string GetParameter(ParameterCollection parameters, string paramName) {
			return SelectParameters[paramName] != null ? SelectParameters[paramName].DefaultValue : null;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Rem update parameter. </summary>
		///
		/// <param name="paramName">	Name of the parameter. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected void RemUpdateParameter(string paramName) { RemParameter(UpdateParameters, paramName); }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Rem select parameter. </summary>
		///
		/// <param name="paramName">	Name of the parameter. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected void RemSelectParameter(string paramName) { RemParameter(SelectParameters, paramName); }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Rem parameter. </summary>
		///
		/// <param name="parameters">	Options for controlling the operation. </param>
		/// <param name="paramName">	Name of the parameter. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected void RemParameter(ParameterCollection parameters, string paramName) {
			if (SelectParameters[paramName] != null) SelectParameters.Remove(SelectParameters[paramName]);
		}

		#endregion

		#region Update

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the field. </summary>
		///
		/// <param name="keys">									The keys. </param>
		/// <param name="fieldName">						Name of the field. </param>
		/// <param name="fieldValue">						The field value. </param>
		/// <param name="returnUpdatedDataSet">	Set the return updated data belongs to. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public DataTable UpdateField(IDictionary keys, string fieldName, object fieldValue, bool returnUpdatedDataSet) {
			KeysFilter kf = new KeysFilter(keys);
			fieldName = "[" + SQLHelperEx.CheckInjects(fieldName) + "]";
			string sql = "UPDATE " + UpdateViewName + " SET "+fieldName+"=@V WHERE " +kf.Filter+
				" AND ("+fieldName+"<>@V OR "+fieldName+" IS NULL OR @V IS NULL)";
			ArrayList parameters = new ArrayList(kf.Params);
			parameters.Insert(0, fieldValue);
			if (returnUpdatedDataSet) sql += ";" + BuildSelect(kf);
			DataSet ds = SQLHelperEx.ExecuteDatasetEx(ConnectionString, sql, parameters.ToArray());
			return ds.Tables.Count > 0 ? ds.Tables[0] : (DataTable)null;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the fields. </summary>
		///
		/// <param name="Fields">	The fields. </param>
		/// <param name="Keys">		The keys. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public DataTable UpdateFields(IDictionary Fields, IDictionary Keys) {
			return UpdateFields(Fields, Keys, "");
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the fields. </summary>
		///
		/// <param name="Fields">			The fields. </param>
		/// <param name="Keys">				The keys. </param>
		/// <param name="postFixSql">	The post fix sql. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public DataTable UpdateFields(IDictionary Fields, IDictionary Keys, string postFixSql) {
			if (Fields.Count == 0) return new DataTable();
			KeysFilter kf = new KeysFilter(Keys);
			string sql = BuildUpdate(kf) + postFixSql;
			DataTable dt = null;
			try {
				dt = SQLHelperEx.UpdateDataTable(Fields,ConnectionString , UpdateCommandTimeout, sql, kf.Params);
			} catch (Dimok.Data.Database.MultipleBaseTableUpdateException) {
				sql = BuildUpdate(kf);
				foreach (DictionaryEntry de in Fields) {
					OrderedDictionary fields = new OrderedDictionary();
					fields.Add(de.Key, de.Value);
					dt = SQLHelperEx.UpdateDataTable(fields, ConnectionString, sql, kf.Params);
				}
				if (sql != "") dt = SQLHelperEx.ExecuteDatasetEx(ConnectionString, sql, kf.Params).Tables[0];
			}
			if (!Keys.IsReadOnly)
				foreach (string key in Keys.Keys)
					Keys[key] = dt.Rows[0][key];
			return dt;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Collection to string. </summary>
		///
		/// <param name="collection">	The collection. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private string CollectionToString(ICollection collection) {
			List<string> list = new List<string>(collection.Count);
			foreach (object o in collection)
				list.Add(o + "");
			return string.Join(",", list.ToArray());
		}
		#endregion

		#region Select

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the select xml. </summary>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public string SelectXml() { return SQLHelperEx.XmlInnerText(((DataView)Select()).Table); }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Prep exception. </summary>
		///
		/// <param name="exc">	The exc. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private void PrepException(Exception exc) {
			if (NamingContainer != null) exc.Data.Add("Container", NamingContainer.GetType().AssemblyQualifiedName);
			exc.Data.Add("Template", GetType().AssemblyQualifiedName);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Selects. </summary>
		///
		/// <param name="whereSql">		The where sql. </param>
		/// <param name="parameters">	Options for controlling the operation. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public DataView Select(string whereSql, params object[] parameters) { return Select(0, whereSql, parameters); }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Selects. </summary>
		///
		/// <param name="commandTimeout">	The command timeout. </param>
		/// <param name="whereSql">				The where sql. </param>
		/// <param name="parameters">			Options for controlling the operation. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public DataView Select(int commandTimeout,string whereSql, params object[] parameters) {
			try {
				string sql = BuildSelect(whereSql);
				Dimok.Common.TraceMe(sql + "(Start)");
				StopWatch.Start();
				if (commandTimeout == 0) commandTimeout = SelectTimeOut;
				DataSet ds = SQLHelperEx.ExecuteDatasetEx(ConnectionString,commandTimeout, sql, parameters);
				StopWatch.Stop();
				Dimok.Common.TraceMe(sql + "(Ended in " + StopWatch.ElapsedMilliseconds + " ms)");
				return AddEmptyRow(ds, EmtyRowPolicy);
			} catch (Exception exc) {
				PrepException(exc);
				throw;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the select ex. </summary>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual DataView SelectEx() { //return this.SelectEx(""); }
			//public virtual DataView SelectEx(string Filter) {// This method will be called automatically if SelectParameters["Filter"] is set
			try{
			string sql = BuildSelect();
			Dimok.Common.TraceMe(sql + "(Start)");
			StopWatch.Start();
			DataSet ds = SQLHelperEx.ExecuteDatasetEx(ConnectionString, SelectTimeOut, sql);
			StopWatch.Stop();
			Dimok.Common.TraceMe(sql + "(Ended in " + StopWatch.ElapsedMilliseconds+" ms)");
			return AddEmptyRow(ds, EmtyRowPolicy);
			} catch (Exception exc) {
				PrepException(exc);
				throw;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the select ex. </summary>
		///
		/// <param name="ID">	The identifier. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual DataView SelectEx(int ID) {// This method will be called automatically if SelectParameters["ID"] is set
			//return SelectEx(ID, "");
			//}
			//public virtual DataView SelectEx(int ID, string Filter) {// This method will be called automatically if SelectParameters["Filter"] is set
			try {
				string sql = BuildSelect(SelectIDFieldName + "=@ID"/*, Filter*/);
				Dimok.Common.TraceMe(sql + "(Start)");
				StopWatch.Start();
				DataSet ds = SQLHelperEx.ExecuteDatasetEx(ConnectionString,SelectTimeOut, sql, ID);
				StopWatch.Stop();
				Dimok.Common.TraceMe(sql + "(Ended in " + StopWatch.ElapsedMilliseconds + " ms)");
				return AddEmptyRow(ds, EmtyRowPolicy);
			} catch (Exception exc) {
				PrepException(exc);
				throw;
			}
		}

		#endregion

		#region Delete

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Deletes the ex described by ID. </summary>
		///
		/// <param name="ID">	The identifier. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual void DeleteEx(object ID) {
			string sql = "DELETE FROM " + DeleteViewName + " WHERE " + DeleteIDFieldName + "=@ID";
			SQLHelperEx.ExecuteNonQueryEx(ConnectionString, sql, ID);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Generic delete method. </summary>
		///
		/// <param name="whereSql">		ID=@ID AND Parent=@ParentID. </param>
		/// <param name="parameters">	ID,ParentID. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual void Delete(string whereSql, params object[] parameters) {
			string sql = "DELETE FROM " + DeleteViewName + " WHERE " + whereSql;
			SQLHelperEx.ExecuteNonQueryEx(ConnectionString,DeleteCommandTimeout, sql, parameters);
		}
		#endregion

		#region Views/Tables

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the name of the select view. </summary>
		///
		/// <value>	The name of the select view. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual string SelectViewName {
			get { throw new Exception("SelectViewName{get} must be implemented in derrived class."); }
			set { 
				//throw new Exception("SelectViewName{set} must be implemented in derrived class."); 
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets or sets the name of the insert view. </summary>
		///
		/// <value>	The name of the insert view. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual string InsertViewName {
			get { throw new Exception("InsertViewName{get} must be implemented in derrived class."); }
			set { throw new Exception("InsertViewName{set} must be implemented in derrived class."); }
		}
		private string _updateViewName = "";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	View name to use in autogenerated UPDATE stattement Defaulted to InsertViewName. </summary>
		///
		/// <value>	The name of the update view. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual string UpdateViewName {
			get { return _updateViewName == "" ? InsertViewName + "" : _updateViewName; }
			set { _updateViewName = value; }
		}
		private string _deleteViewName = "";

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	View name to use in autogenerated DELETE stattement Defaulted to InsertViewName. </summary>
		///
		/// <value>	The name of the delete view. </value>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public virtual string DeleteViewName {
			get { return _deleteViewName == "" ? InsertViewName + "" : _deleteViewName; }
			set { _deleteViewName = value; }
		}

		#endregion

		#region Helpers

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Adds a row if empty. </summary>
		///
		/// <param name="dv">	The dv. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected DataView AddRowIfEmpty(IEnumerable dv) { return AddRowIfEmpty((DataView)dv); }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Adds a row if empty. </summary>
		///
		/// <param name="ds">	The ds. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected DataView AddRowIfEmpty(DataSet ds) { return AddRowIfEmpty(ds.Tables[0].DefaultView); }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Adds a row if empty. </summary>
		///
		/// <param name="dv">	The dv. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected DataView AddRowIfEmpty(DataView dv) {
			AddEmptyRow(dv.Table, true);
			return dv;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Adds an empty row to 'emptyRowPolicy'. </summary>
		///
		/// <param name="ds">							The ds. </param>
		/// <param name="emptyRowPolicy">	The empty row policy. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected DataView AddEmptyRow(DataSet ds, EmptyRowPolicyType emptyRowPolicy) {
			return AddEmptyRow(ds.Tables[0].DefaultView, emptyRowPolicy);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Adds an empty row to 'emptyRowPolicy'. </summary>
		///
		/// <param name="dv">							The dv. </param>
		/// <param name="emptyRowPolicy">	The empty row policy. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected DataView AddEmptyRow(DataView dv, EmptyRowPolicyType emptyRowPolicy) {
			AddEmptyRow(dv.Table, emptyRowPolicy);
			return dv;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Adds an empty row to 'emptyRowPolicy'. </summary>
		///
		/// <param name="dt">							The dt. </param>
		/// <param name="emptyRowPolicy">	The empty row policy. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected DataTable AddEmptyRow(DataTable dt, EmptyRowPolicyType emptyRowPolicy) {
			if (DataTableName != "" ) dt.TableName = DataTableName;
			if (emptyRowPolicy == EmptyRowPolicyType.None) return dt;
			if ((emptyRowPolicy & EmptyRowPolicyType.OnlyIfEmpty) == EmptyRowPolicyType.OnlyIfEmpty && dt.Rows.Count > 0) 
				return dt;
			if( dt.Rows.Count == 1 && (emptyRowPolicy & EmptyRowPolicyType.OnlyIfMoreThenOne) == EmptyRowPolicyType.OnlyIfMoreThenOne )
				return dt;
			return AddEmptyRow(dt, (emptyRowPolicy & EmptyRowPolicyType.Last) == EmptyRowPolicyType.Last);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Adds a new line. </summary>
		///
		/// <param name="dt">	The dt. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected DataTable AddNEWLine_(DataTable dt) {
			//if(AddNewLine) dt.Rows.Add(BuildEmptyRowArray(dt));
			return dt;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Adds an empty row to 'emptyRowPolicy'. </summary>
		///
		/// <param name="dt">		The dt. </param>
		/// <param name="last">	true to last. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected DataTable AddEmptyRow(DataTable dt, bool last) {
			if (DataTableName != "") dt.TableName = DataTableName;
			dt.Rows.InsertAt(dt.NewRow(), last ? dt.Rows.Count : 0);
			return dt;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Builds an empty row array. </summary>
		///
		/// <param name="dt">	The dt. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected object[] BuildEmptyRowArray(DataTable dt) {
			ArrayList al = new ArrayList();
			foreach (DataColumn col in dt.Columns)
				al.Add(col.DefaultValue);
			return al.ToArray();
		}
		#endregion
	}
}